#!/usr/bin/env python
# -*- coding:utf-8 -*-
'''基于sqlite3的属性表

功能:
    1. 指定属性表文件
    2. 保存指定key值
    3. 获得指定key值

历史:
    2018-11-30 姚彧 创建
'''

__all__ = ['AttributesTable']

import os
import sqlite3


class AttributesTable(object):
    def __init__(self, filename, table = 'tblStatus'):
        connection = sqlite3.connect(filename)
        assert isinstance(connection, sqlite3.Connection)
        assert os.path.isfile(filename)
        self.dbname = filename
        self.table = table
        self.__init_db(connection)

    def __init_db(self, connection):
        execute = connection.execute
        execute('create table IF NOT EXISTS %s (key not null UNIQUE, value);' % self.table)
        if self.table == 'tblStatus':
            self.set_value('License', 'MIT')
            self.set_value('Author', '姚彧')
            self.set_value('email', 'yao_yu@126.com')
            self.set_value('Version', '1.0.0.20181130')

    def get_value(self, key, default = None):
        key = str(key).strip()
        connection = sqlite3.connect(self.dbname)
        cursor = connection.execute("select value from %s where key = ?" % self.table, [key])
        assert isinstance(cursor, sqlite3.Cursor)
        res = cursor.fetchone()
        if res:
            return res[0]
        return default

    def get_bool_value(self, key, default = None) -> bool:
        return self.boolean(self.get_value(key, default))

    def get_float_value(self, key, default = 0) -> float:
        try:
            return float(self.get_value(key, default))
        except:
            return default

    def set_value(self, key, value):
        key = str(key).strip()
        connection = sqlite3.connect(self.dbname)
        execute = connection.execute
        assert isinstance(connection, sqlite3.Connection)
        cursor = execute("select value from %s where key = ?" % self.table, [key])
        assert isinstance(cursor, sqlite3.Cursor)
        row = cursor.fetchone()
        if row is not None:
            if row[0] != value:
                cursor = execute("update %s set value = ? where key = ?" % self.table, [value, key])
            else:
                return
        else:
            cursor = execute("insert into %s values (?, ?)" % self.table, [key, value])
        connection.commit()

    # ------------------------------------------------------------
    # 工具函数
    #
    @staticmethod
    def boolean(value):
        if value:
            return value not in ('0', 'False')
        return False


if __name__ == '__main__' :
    def main():
        pt = AttributesTable(r'd:/temp/180724.db')
        key, value = 1, '66'
        pt.set_value(key, value)
        assert (pt.get_value(key) == value)
        print(pt.get_value('Author'))
    main()